 with last_week_network_data as(
  select
     customer_code,                                                           --客户编码
     max(customer_name) as customer_name,                                    --客户名字
     concat_ws(',',collect_set(ordersource_code)) as ordersource_code,       --平台ID
     ordersource_name,                                                       --平台名字
     max(customer_type_id) as customer_type_id,
     max(customer_type_name) as customer_type_name,
     max(customer_category) as customer_category,
     max(customer_category_name) as customer_category_name,
     max(customer_attribute_id) as customer_attribute_id,
     max(customer_attribute_name) as customer_attribute_name,
     max(subordinate_agent_code) as subordinate_agent_code,
     max(subordinate_agent_name) as subordinate_agent_name,
     max(first_franchisee_id) as first_franchisee_id,
     max(first_franchisee_code) as first_franchisee_code,
     max(first_franchisee_name) as first_franchisee_name,
     max(customer_belong_network_id) as customer_belong_network_id,
     max(customer_belong_network_name) as customer_belong_network_name,
     max(customer_province_id) as customer_province_id,
     max(customer_province_desc) as customer_province_desc,
     max(customer_city_id) as customer_city_id,
     max(customer_city_desc) as customer_city_desc,
     max(customer_area_id) as customer_area_id,
     max(customer_area_desc) as customer_area_desc,
     sum(if(day_customer_sum is not null,day_customer_sum,0)) as week_customer_waybill_sum,   --客户发货运单总量
     min(customer_signed_time) as customer_signed_time
  from jms_dm.dm_customer_base_detail_dt
  where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) - 1 end)
  and dt< date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) - 1 end)
  and day_customer_sum>0
  group by customer_code,ordersource_name
  ),
  --今日数据
  week_customer as(
  select
      nvl(a.customer_code,b.customer_code) as customer_code,                                                                                                    --客户编码
      nvl(a.customer_name,b.customer_name) as customer_name,                                                                                                    --客户名字
   case when nvl(a.week_customer_waybill_sum,0)/7 >5000.0 then 'A'
            when nvl(a.week_customer_waybill_sum,0)/7 >1000.0 then 'B'
            when nvl(a.week_customer_waybill_sum,0)/7 >200.0 then 'C'
            when nvl(a.week_customer_waybill_sum,0)/7 >50.0 then 'D'
            else 'E' end  as customer_level,                                                                               --客户等级
   case when nvl(b.week_customer_waybill_sum,0)/7 >5000.0 then 'A'
        when nvl(b.week_customer_waybill_sum,0)/7 >1000.0 then 'B'
        when nvl(b.week_customer_waybill_sum,0)/7 >200.0 then 'C'
        when nvl(b.week_customer_waybill_sum,0)/7 >50.0 then 'D'
        else 'E' end  as last_week_customer_level,                                                                               --客户等级
      nvl(a.ordersource_code,b.ordersource_code) as ordersource_code,                                                      --平台ID
      nvl(a.ordersource_name,b.ordersource_name) as ordersource_name,                                                      --平台名字
      nvl(a.customer_type_id,b.customer_type_id) as customer_type_id  ,                                                    -- 客户类型
      nvl(a.customer_type_name,b.customer_type_name) as customer_type_name,                                                --客户类型名字
      nvl(a.customer_category,b.customer_category) as customer_category,                                                   --客户类别
      nvl(a.customer_category_name,b.customer_category_name) as customer_category_name,
      nvl(a.customer_attribute_id,b.customer_attribute_id) as customer_attribute_id ,                                      --客户属性ID
      nvl(a.customer_attribute_name,b.customer_attribute_name) as customer_attribute_name,                                 --客户属性名字
      nvl(a.subordinate_agent_name,b.subordinate_agent_name) as subordinate_agent_name,                                    --所属代理区
      nvl(a.subordinate_agent_code,b.subordinate_agent_code) as subordinate_agent_code,                                    --所属代理区code
      nvl(a.first_franchisee_id,b.first_franchisee_id) as first_franchisee_id    ,                                         -- 一级加盟商id',
      nvl(a.first_franchisee_code,b.first_franchisee_code) as first_franchisee_code,                                       -- '一级加盟商code',
      nvl(a.first_franchisee_name,b.first_franchisee_name) as first_franchisee_name  ,                                     -- 一级加盟商',
      nvl(a.customer_belong_network_id,b.customer_belong_network_id) as customer_belong_network_id ,                       -- 客户所属网点ID
      nvl(a.customer_belong_network_name,b.customer_belong_network_name) as customer_belong_network_name ,                 --  客户所属网点名字
      nvl(a.customer_province_id,b.customer_province_id) as customer_province_id   ,                                       -- 客户所属省份id
      nvl(a.customer_province_desc,b.customer_province_desc) as customer_province_desc ,                                   --客户所属省份名字
      nvl(a.customer_city_id,b.customer_city_id) as customer_city_id     ,                                                 -- 客户所属城市id
      nvl(a.customer_city_desc,b.customer_city_desc) as customer_city_desc,                                                --  客户所属城市名字',
      nvl(a.customer_area_id,b.customer_area_id) as customer_area_id   ,                                                   --  客户所属区域id
      nvl(a.customer_area_desc,b.customer_area_desc) as customer_area_desc ,                                               -- 客户所属区县的名字',
      nvl(a.week_customer_waybill_sum,0) as week_customer_waybill_sum ,                                                    --运单总量
      nvl(b.week_customer_waybill_sum,0) as last_week_customer_waybill_sum ,                                                    --上周运单总量
      nvl(a.customer_signed_time,b.customer_signed_time) as customer_signed_time,                                            --客户创建时间
      if(a.customer_signed_time>= date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)+7) and a.customer_signed_time <=date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)) and nvl(a.week_customer_waybill_sum,0)>0 ,1,0)as is_new,          --是否为新增
      if(a.customer_signed_time>= date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)+7) and a.customer_signed_time<=date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)) and nvl(a.week_customer_waybill_sum,0)>0 ,nvl(a.week_customer_waybill_sum,0) ,0)as new_customer_waybill_sum,  --新增运单总量
  case when  a.ordersource_name is null  and b.ordersource_name is not null then 1
       else 0 end as is_loss,                                                                                                                --是否流失
  case when  a.ordersource_name is null  and b.ordersource_name is not null then b.week_customer_waybill_sum
     else 0 end as loss_waybill_sum,                                                                                                         --流失运单总量
 case when  a.customer_signed_time < date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)+7) and a.ordersource_name is not null  and b.ordersource_name is null then 1
       else 0 end as is_retrieve,                                                                                                            --是否挽回
  case when  a.customer_signed_time < date_sub('{{ execution_date | cst_ds }}',pmod(datediff('{{ execution_date | cst_ds }}','1900-01-08'),7)+7) and a.ordersource_name is not null  and b.ordersource_name is null  then a.week_customer_waybill_sum
     else 0 end as retrieve_waybill_sum                                                                                                     --挽回运单总量
  from (
  select
     customer_code,                                                         --客户编码
     max(customer_name) as customer_name,                                   --客户名字
     concat_ws(',',collect_set(ordersource_code)) as ordersource_code,                                                      --平台ID
     ordersource_name,                             --平台名字
     max(customer_type_id) as customer_type_id  ,                                                    -- 客户类型
     max(customer_type_name) as customer_type_name,                         --客户类型名字
     max(customer_category) as customer_category,                                                     --客户类别
     max(customer_category_name) as customer_category_name,
     max(customer_attribute_id) as customer_attribute_id ,                                                --客户属性ID
     max(customer_attribute_name) as customer_attribute_name,               --客户属性名字
     max(subordinate_agent_code) as subordinate_agent_code,                 --所属代理区code
     max(subordinate_agent_name) as subordinate_agent_name,                 --所属代理区
     max(first_franchisee_id) as first_franchisee_id    ,                   -- 一级加盟商id',
     max(first_franchisee_code) as first_franchisee_code,                   -- '一级加盟商code',
     max(first_franchisee_name) as first_franchisee_name  ,                 -- 一级加盟商',    --客户所属加盟商
     max(customer_belong_network_id) as customer_belong_network_id ,                                           -- 客户所属网点ID
     max(customer_belong_network_name) as customer_belong_network_name ,    --  客户所属网点名字
     max(customer_province_id) as customer_province_id   ,                  -- 客户所属省份id
 max(customer_province_desc) as customer_province_desc ,                    --客户所属省份名字
     max(customer_city_id) as customer_city_id     ,                        -- 客户所属城市id
 max(customer_city_desc) as customer_city_desc,                             --  客户所属城市名字',
     max(customer_area_id) as customer_area_id   ,                          --  客户所属区域id
 max(customer_area_desc) as customer_area_desc ,                            -- 客户所属区县的名字',
 sum(nvl(day_customer_sum,0)) as week_customer_waybill_sum ,                       --运单总量
 min(customer_signed_time) as customer_signed_time                         --客户创建时间
  from  jms_dm.dm_customer_base_detail_dt
  where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) - 1 end)
  and dt< date_add('{{ execution_date | date_add(1) | cst_ds }}',1 - case when dayofweek('{{ execution_date | date_add(1) | cst_ds }}') = 1 then 7 else dayofweek('{{ execution_date | date_add(1) | cst_ds }}') - 1 end)
  and day_customer_sum>0
  group by customer_code,ordersource_name
  )   a
  full join  last_week_network_data b
  on a.customer_code=b.customer_code
  and a.ordersource_name=b.ordersource_name
  where (a.ordersource_name is not null or b.ordersource_name is not null)
  ),
 network_customer as(
 select
             'network' as org_level,
             'Week' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,--代理区名字
             today.first_franchisee_code,  --加盟商code
             today.first_franchisee_name,  --加盟商名字
             today.customer_belong_network_id,  --客户所属网点ID
             today.customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt,4) as customer_cnt_rate,
             yesterday.last_week_customer_cnt,
             today.week_customer_waybill_sum,--发件客户运单总量
             today_sum.today_week_customer_waybill_sum,
             round(today.week_customer_waybill_sum/today_sum.today_week_customer_waybill_sum,4) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_week_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.last_week_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.last_week_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
            round(today.retrieve_cnt/yesterday.last_week_customer_cnt,4) as retrieve_customer_rate  --挽回客户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             subordinate_agent_code,
             max(subordinate_agent_name) as subordinate_agent_name,
             first_franchisee_code,
             max(first_franchisee_name) as first_franchisee_name,
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             ordersource_name,  --平台名字
             sum(if(week_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(week_customer_waybill_sum,0))  as week_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from week_customer a
            -- where a.week_customer_waybill_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id
      )today
  left join (
           select
           customer_belong_network_id,       --客户所属网点ID
           max(customer_belong_network_name) as customer_belong_network_name,   --客户所属网点名字
                  ordersource_name,
                  last_week_customer_level,
           count( distinct customer_code) as last_week_customer_cnt
           from week_customer
           where last_week_customer_waybill_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) - 1 end)
--            and dt< date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) - 1 end)
--           and day_customer_sum>0
           group by customer_belong_network_id,ordersource_name,last_week_customer_level
        )yesterday
on today.customer_belong_network_id=yesterday.customer_belong_network_id
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_week_customer_level
join (
             select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             count( distinct customer_code) as today_customer_cnt,    --发件客数
             sum(week_customer_waybill_sum)  as today_week_customer_waybill_sum  --发件客户运单总量
             from week_customer a
             where a.week_customer_waybill_sum>0
             group by ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id
      )today_sum
  on today.ordersource_name=today_sum.ordersource_name
  and today.customer_belong_network_id=today_sum.customer_belong_network_id
 ),
 network_customer_sum as (
 select
            'network' as org_level,
             'Week' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             first_franchisee_code,  --加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --加盟商名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_week_customer_cnt) as last_week_customer_cnt,
             sum(week_customer_waybill_sum) as week_customer_waybill_sum,--发件客户运单总量
             max(today_week_customer_waybill_sum) as today_week_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_week_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum)as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_week_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_week_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
            round(sum(retrieve_cnt)/sum(last_week_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
 from network_customer
 group by ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id

 ),
 --加盟商
 fran_customer as(
          select
             'fran' as org_level,
             'Week' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,--代理区名字
             today.first_franchisee_code,  --加盟商code
             today.first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt,4) as customer_cnt_rate,
             yesterday.last_week_customer_cnt,
             today.week_customer_waybill_sum,--发件客户总运单量
             today_sum.today_week_customer_waybill_sum,
             round(today.week_customer_waybill_sum/today_week_customer_waybill_sum) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_week_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.last_week_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
            round(today.loss_cnt/yesterday.last_week_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
            round(today.retrieve_cnt/yesterday.last_week_customer_cnt,4) as retrieve_customer_rate  --挽回客户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             subordinate_agent_code,   --代理区code
             ordersource_name,  --平台名字
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             first_franchisee_code,  --客户所加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --加盟商名字
             sum(if(week_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(week_customer_waybill_sum,0))  as week_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from week_customer a
             --where a.week_customer_waybill_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code,first_franchisee_code
      )today
  left join (
           select
           first_franchisee_code,       --客户所属加盟商code
           max(first_franchisee_name) as first_franchisee_name,   --客户所属加盟商名字
                  ordersource_name,
                  last_week_customer_level,
           count(distinct customer_code) as last_week_customer_cnt
           from week_customer
           where last_week_customer_waybill_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) - 1 end)
--            and dt< date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) - 1 end)
--           and day_customer_sum>0
           group by first_franchisee_code,ordersource_name,last_week_customer_level
        )yesterday
on today.first_franchisee_code=yesterday.first_franchisee_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_week_customer_level
join (
             select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             first_franchisee_code,  --客户所属加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --客户所属加盟商名字
             count( distinct customer_code) as today_customer_cnt,    --发件客数
             sum(week_customer_waybill_sum)  as today_week_customer_waybill_sum  --发件客户运单总量
             from week_customer a
             where a.week_customer_waybill_sum>0
             group by ordersource_name,subordinate_agent_code,first_franchisee_code
      )today_sum
  on today.ordersource_name=today_sum.ordersource_name
  and today.first_franchisee_code=today_sum.first_franchisee_code
 ),
  fran_customer_sum as (
 select
            'fran' as org_level,
             'Week' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             first_franchisee_code,  --加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_week_customer_cnt) as last_week_customer_cnt,
             sum(week_customer_waybill_sum) as week_customer_waybill_sum,--发件客户运单总量
             max(today_week_customer_waybill_sum) as today_week_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_week_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum)as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_week_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_week_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
            round(sum(retrieve_cnt)/sum(last_week_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
 from fran_customer
 group by ordersource_name,subordinate_agent_code,first_franchisee_code

 ),

--代理区

agent_customer as(
          select
             'agent' as org_level,
             'Week' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --客户所属代理区code
             today.subordinate_agent_name,  --客户所属代理区名字
             null as first_franchisee_code,  --加盟商code
             null as first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt,4) as customer_cnt_rate,
             yesterday.last_week_customer_cnt,
             today.week_customer_waybill_sum,--发件客户总单量
             today_sum.today_week_customer_waybill_sum,
             round(today.week_customer_waybill_sum/today_sum.today_week_customer_waybill_sum,4) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_week_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户重量
             round(today.new_cnt/yesterday.last_week_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.last_week_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.last_week_customer_cnt,4) as retrieve_customer_rate  --挽回客户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             subordinate_agent_code,  --客户所加盟商code
             max(subordinate_agent_name) as subordinate_agent_name,  --客户所属网点名字
             ordersource_name,  --平台名字
            sum(if(week_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(week_customer_waybill_sum,0))  as week_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from week_customer a
             --where a.week_customer_waybill_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code
      )today
  left join (
           select
           subordinate_agent_code,       --客户所属加盟商code
           max(subordinate_agent_name) as subordinate_agent_name,   --客户所属加盟商名字
                  ordersource_name,
           last_week_customer_level,
           count( distinct customer_code) as last_week_customer_cnt
           from week_customer
           where last_week_customer_waybill_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt >=date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-14)) - 1 end)
--            and dt< date_add(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7),1 - case when dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) = 1 then 7 else dayofweek(date_add('{{ execution_date | date_add(1) | cst_ds }}',-7)) - 1 end)
--           and day_customer_sum>0
           group by subordinate_agent_code,ordersource_name,last_week_customer_level
        )yesterday
on today.subordinate_agent_code=yesterday.subordinate_agent_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_week_customer_level
join (
             select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --客户所属代理区code
             max(subordinate_agent_code) as first_franchisee_name,  --客户所属代理区名字
             count( distinct customer_code) as today_customer_cnt,    --发件客数
             sum(week_customer_waybill_sum)  as today_week_customer_waybill_sum  --发件客户运单总量
             from week_customer a
             where a.week_customer_waybill_sum>0
             group by ordersource_name,subordinate_agent_code
      )today_sum
  on today.ordersource_name=today_sum.ordersource_name
  and today.subordinate_agent_code=today_sum.subordinate_agent_code
),
agent_customer_sum as (
 select
            'agent' as org_level,
             'Week' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             null as first_franchisee_code,  --加盟商code
             null as first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_week_customer_cnt) as last_week_customer_cnt,
             sum(week_customer_waybill_sum) as week_customer_waybill_sum,--发件客户运单总量
             max(today_week_customer_waybill_sum) as today_week_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_week_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum)as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_week_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_week_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
            round(sum(retrieve_cnt)/sum(last_week_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
 from agent_customer
 group by ordersource_name,subordinate_agent_code

 )

insert overwrite table jms_dm.dm_customer_changes_week_dt partition(dt,org_level)
 select
       date_type,
       customer_level,           --客户等级
       ordersource_code,         --客户平台ID
       ordersource_name,  --平台名字
       subordinate_agent_code,
       subordinate_agent_name,
       first_franchisee_code,
       first_franchisee_name,
       customer_belong_network_id,  --客户所属网点ID
       customer_belong_network_name,  --客户所属网点名字
       customer_cnt,    --发件客数
       today_customer_cnt,
       customer_cnt_rate,
       last_week_customer_cnt,
       week_customer_waybill_sum,--发件客户运单总量
       today_week_customer_waybill_sum,
       customer_waybill_rate,
       customer_up_rate,  --客户提升率
       new_cnt,      --新增客户数
       new_customer_waybill_sum,  --新增客户运单总量
       new_customer_rate,  --新增客户率
       loss_cnt,   --流失客户数
       loss_waybill_sum,  --流失客户运单总量
       loss_customer_rate,   --流失率
       retrieve_cnt,  --挽回客户数
       retrieve_waybill_sum,   --挽回客户运单总量
       retrieve_customer_rate,  --挽回率
       '{{ execution_date | cst_ds }}' as dt2,
       org_level as org_level2,
       '{{ execution_date | cst_ds }}' as dt,
        org_level
  from (
       select
              org_level,
               date_type,
               customer_level,           --客户等级
               ordersource_code,         --客户平台ID
               ordersource_name,  --平台名字
               subordinate_agent_code,
               subordinate_agent_name,
               first_franchisee_code,
               first_franchisee_name,
               customer_belong_network_id,  --客户所属网点ID
               customer_belong_network_name,  --客户所属网点名字
               customer_cnt,    --发件客数
               today_customer_cnt,
               customer_cnt_rate,
               last_week_customer_cnt,
               week_customer_waybill_sum,--发件客户运单总量
               today_week_customer_waybill_sum,
               customer_waybill_rate,
               customer_up_rate,  --客户提升率
               new_cnt,      --新增客户数
               new_customer_waybill_sum,  --新增客户运单总量
               new_customer_rate,  --新增客户率
               loss_cnt,   --流失客户数
               loss_waybill_sum,  --流失客户运单总量
               loss_customer_rate,  --流失率
               retrieve_cnt,  --挽回客户数
               retrieve_waybill_sum,   --挽回客户运单总量
               retrieve_customer_rate       --挽回率
         from network_customer
  union all
  select
              org_level,
               date_type,
               customer_level,           --客户等级
               ordersource_code,         --客户平台ID
               ordersource_name,  --平台名字
               subordinate_agent_code,
               subordinate_agent_name,
               first_franchisee_code,
               first_franchisee_name,
               customer_belong_network_id,  --客户所属网点ID
               customer_belong_network_name,  --客户所属网点名字
               customer_cnt,    --发件客数
               today_customer_cnt,
               customer_cnt_rate,
               last_week_customer_cnt,
               week_customer_waybill_sum,--发件客户运单总量
               today_week_customer_waybill_sum,
               customer_waybill_rate,
               customer_up_rate,  --客户提升率
               new_cnt,      --新增客户数
               new_customer_waybill_sum,  --新增客户运单总量
               new_customer_rate,  --新增客户率
               loss_cnt,   --流失客户数
               loss_waybill_sum,  --流失客户运单总量
               loss_customer_rate,  --流失率
               retrieve_cnt,  --挽回客户数
               retrieve_waybill_sum,   --挽回客户运单总量
               retrieve_customer_rate       --挽回率
         from network_customer_sum
  union all
        select
              org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_week_customer_cnt,
              week_customer_waybill_sum,--发件客户运单总量
              today_week_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
         from fran_customer
      union all
	  select
              org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_week_customer_cnt,
              week_customer_waybill_sum,--发件客户运单总量
              today_week_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
         from fran_customer_sum
      union all
         select
               org_level,
               date_type,
               customer_level,           --客户等级
               ordersource_code,         --客户平台ID
               ordersource_name,  --平台名字
               subordinate_agent_code,
               subordinate_agent_name,
               first_franchisee_code,
               first_franchisee_name,
               customer_belong_network_id,  --客户所属网点ID
               customer_belong_network_name,  --客户所属网点名字
               customer_cnt,    --发件客数
               today_customer_cnt,
               customer_cnt_rate,
               last_week_customer_cnt,
               week_customer_waybill_sum,--发件客户运单总量
               today_week_customer_waybill_sum,
               customer_waybill_rate,
               customer_up_rate,  --客户提升率
               new_cnt,      --新增客户数
               new_customer_waybill_sum,  --新增客户运单总量
               new_customer_rate,  --新增客户率
               loss_cnt,   --流失客户数
               loss_waybill_sum,  --流失客户运单总量
               loss_customer_rate,  --流失率
               retrieve_cnt,  --挽回客户数
               retrieve_waybill_sum,   --挽回客户运单总量
               retrieve_customer_rate       --挽回率
  from agent_customer
  union all
  select
               org_level,
               date_type,
               customer_level,           --客户等级
               ordersource_code,         --客户平台ID
               ordersource_name,  --平台名字
               subordinate_agent_code,
               subordinate_agent_name,
               first_franchisee_code,
               first_franchisee_name,
               customer_belong_network_id,  --客户所属网点ID
               customer_belong_network_name,  --客户所属网点名字
               customer_cnt,    --发件客数
               today_customer_cnt,
               customer_cnt_rate,
               last_week_customer_cnt,
               week_customer_waybill_sum,--发件客户运单总量
               today_week_customer_waybill_sum,
               customer_waybill_rate,
               customer_up_rate,  --客户提升率
               new_cnt,      --新增客户数
               new_customer_waybill_sum,  --新增客户运单总量
               new_customer_rate,  --新增客户率
               loss_cnt,   --流失客户数
               loss_waybill_sum,  --流失客户运单总量
               loss_customer_rate,  --流失率
               retrieve_cnt,  --挽回客户数
               retrieve_waybill_sum,   --挽回客户运单总量
               retrieve_customer_rate       --挽回率
  from agent_customer_sum
  ) a
  distribute by 10;